package MaintMenu.SyncInventoryToCat;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: Jason
 * Date: 8/11/13
 * Time: 6:24 PM
 * To change this template use File | Settings | File Templates.
 */
public class UpdInvPL {



    public UpdInvPL(){

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = "dbLI";
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection conli = DriverManager.getConnection(dbURL, "", "");
            Statement stliPL = conli.createStatement();
            Statement stliUpd = conli.createStatement();
            try{
                String[] tbl = new String[]{"tblinventory","tblbcn","tblltn","tblhfn","tblmrn","tblcln","tblotn"};

                String pl="";
                String oPL="";
                String orgPL="";
                String sql="";
                String updpl=pl;
                String plField="";
                int recordUpd=0;


                int z=0;

                for (z=0; z<7 ;z++) {
                    if (z==0){
                        plField="Partslink";
                    }else if (z>0) {
                        plField="PARTLINK";
                    }
                    stliPL.execute("select * from "+tbl[z]+"");
                    ResultSet resultSet1 = stliPL.getResultSet();

//                    System.out.print("<Start table :"+tbl[z]+" "+plField+">\t\t");
                    if (resultSet1 != null)  {

                        while (resultSet1.next()) {
                            oPL=resultSet1.getString("PART#");
                            pl= resultSet1.getString(plField);
                            String orignialPL=pl;


//                            orgPL=pl;


                            if(pl==null){
                                updpl="0";
                            }else if (pl.length() > 9) {

//                                System.out.println("B:"+pl);
                                pl=pl.replaceAll(" ","");
                                pl=pl.replaceAll("-", "");
                                pl=pl.replaceAll("/","");
//                                System.out.println("A:"+pl);
//                                try{
//                                    sql="update "+tbl[z]+" set "+plField+"='"+pl+"' where [part#]='"+oPL+"'";
////                                    System.out.println(sql);
//
//                                    stliUpd.execute(sql);
////                                    System.out.println("L:"+updpl);
//
//                                    //                            System.out.println(".");
//                                }catch (SQLException er){
//                                    System.out.println(er);
//                                }

                                if (pl.length() > 9) {
                                pl = pl.substring(0, 9);
//                                    System.out.println("C:"+updpl);
//                                    try{
//                                        sql="update "+tbl[z]+" set "+plField+"='"+updpl+"' where [part#]='"+oPL+"'";
////                                System.out.println(sql);
//
//                                        stliUpd.execute(sql);
////                                        System.out.println("L:"+updpl);
//
//                                        //                            System.out.println(".");
//                                    }catch (SQLException er){
//                                        System.out.println(er);
//                                    }
                                }
    //                            System.out.println(updpl);




                                try{
                                    sql="update "+tbl[z]+" set "+plField+"='"+pl+"' where [part#]='"+oPL+"'";
//                                System.out.println("Unchange items:except remove space and -/"+sql);
//                                    System.out.println("ORG PL:"+orignialPL+ "  NEW PL:"+pl);

                                    stliUpd.execute(sql);
                                    recordUpd++;

                                    //                            System.out.println(".");
                                }catch (SQLException er){
//                                    System.out.println(er);
                                }
                            }
                        }
                    }
                }
//                System.out.println("PL Cleanup Done");
//                System.out.println("Total PL# with Problem :"+recordUpd);
            }catch (SQLException er){
//                System.out.println(er);
            }
            stliPL.close();
            stliUpd.close();
            conli.close();


        }catch (SQLException er){
//            System.out.println(er);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
//        System.out.println("*TBLINV PL CLEANUP COMPLETE*");


    }
}
